USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetProductsXml')
	DROP FUNCTION dbo.GetProductsXml
GO


CREATE FUNCTION [dbo].[GetProductsXml] () 
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@result			varchar(max)

	SET @result = '<PRODUCTS><entry id="0" unitprice="0.00"></entry>'

	select @result = @result + '<entry id="' + CAST(ProductID AS VARCHAR) + '" unitprice="'+ CAST(UnitPrice AS VARCHAR)+'"><![CDATA[' +  ProductDesc + ']]></entry>'
	FROM TR_PRODUCT 
	WHERE ISACTIVE = 1
	Order by ProductDesc
	
	SET @result = @result + '</PRODUCTS>'

	return @result

END


-- SELECT dbo.GetProductsXml()